﻿<AddDataFormTemplateSpec 
	xmlns="bb_appfx_adddataformtemplate"
	xmlns:common="bb_appfx_commontypes" 
	ID="d27bd621-7ab4-4611-ba53-25747d0fe856" 
	Name="FundraisingPurpose Add Data Form Custom"
	Description="Add Fundraising Purpose Custom For World Vision" 
	Author="Blackbaud Product Development"
	DataFormInstanceID="92f4dd42-29a2-4328-bae6-1fd1821a8712" 
	RecordType="Designation"
	common:SecurityUIFolder="Service Bus Adapter"
	>

	<SPDataForm>
		<!-- describe the preload SP (if applicable), which should be used to fetch field default values from the database.  If no
		preload is needed, then this section can be removed -->
		<!-- describe the save SP -->
		<SaveImplementation SPName="USP_DATAFORMTEMPLATE_ADD_FUNDRAISINGPURPOSE">
			<common:CreateProcedureSQL>
				<![CDATA[
create procedure dbo.USP_DATAFORMTEMPLATE_ADD_FUNDRAISINGPURPOSE
(
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
@Description nvarchar(255) = '',
@SubReg1 nvarchar(255) = '',
@LookupId nvarchar(255) = '', /*Also ("Segment 1" or "ITEM_NUMBER") UserId*/
@Priority nvarchar(100) = '',
@ItemCategorySegment2 nvarchar(255) = '',
@FundraisingTeam nvarchar(255) = '',
@IsActive bit = NULL,
@IsRevenueDesignation bit = NULL,
@Goal money = NULL,
@StartDate datetime = NULL,
@EndDate datetime = NULL,
@ReceivedAmount money = NULL

)
as

set nocount on;

if @ID is null
    set @ID = newid()

if @CHANGEAGENTID is null  
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()

begin try




--DESIGNATIONLEVELTYPE needs a "Fund" record - should be a stock CRM record
--CAMPAIGN needs 'Rebuilding Hope at Home')		
--CAMPAIGN needs 'World Vision For Every Child')		
/*SELECT * FROM CAMPAIGN
UPDATE CAMPAIGN SET USERID = 'WVFEC', NAME = 'World Vision For Every Child' 
WHERE ID = 'A7E85228-5CBE-4BA9-ACD6-DC81518ABE53'
UPDATE CAMPAIGN SET USERID = 'RHH', NAME = 'Rebuilding Hope At Home' 
WHERE ID = 'DDA5E973-D673-41DE-A1B8-AC61235FCBBA'
SELECT * FROM DESIGNATIONLEVELTYPE
*/



/*
SELECT * FROM DESIGNATION WHERE DATEADDED > '2012-07-01'
SELECT * FROM DESIGNATIONLEVEL WHERE DATEADDED > '2012-07-01'
SELECT * FROM DESIGNATIONREPORT2CODE WHERE DATEADDED > '2012-07-01'
SELECT * FROM DESIGNATIONUSECODE WHERE DATEADDED > '2012-07-01'
SELECT * FROM DESIGNATION WHERE DATEADDED > '2012-07-01'
SELECT * FROM CAMPAIGNSUBPRIORITY WHERE DATEADDED > '2012-07-01'
SELECT * FROM CAMPAIGNSUBPRIORITYNAMECODE WHERE DATEADDED > '2012-07-01'
SELECT * FROM CAMPAIGNPRIORITYTYPECODE WHERE DATEADDED > '2012-07-01'
SELECT * FROM DESIGNATIONCAMPAIGN WHERE DATEADDED > '2012-07-01'

DELETE FROM CAMPAIGNSUBPRIORITYNAMECODE WHERE DATEADDED > '2012-07-01'
GO
DELETE FROM CAMPAIGNPRIORITY WHERE DATEADDED > '2012-07-01'
GO
DELETE FROM DESIGNATIONCAMPAIGN WHERE DATEADDED > '2012-07-01'
GO
DELETE FROM CAMPAIGNSUBPRIORITY WHERE DATEADDED > '2012-07-01'
GO
DELETE FROM DESIGNATION WHERE DATEADDED > '2012-07-01'
GO
DELETE FROM DESIGNATIONLEVEL WHERE DATEADDED > '2012-07-01'
GO
DELETE FROM DESIGNATIONREPORT2CODE WHERE DATEADDED > '2012-07-01'
GO
DELETE FROM DESIGNATIONUSECODE WHERE DATEADDED > '2012-07-01'
GO
DELETE FROM CAMPAIGNPRIORITYTYPECODE WHERE DATEADDED > '2012-07-01'
GO

*/


/*
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
*/




/*
SET @Description  = 'Ethiopia MFI Fund (WISDOM)'
SET @SubReg1 = 'Ethiopia'
SET @LookupId = 'D50194029' --Also ("Segment 1" or "ITEM_NUMBER") UserId
SET @Priority = 'High'
SET @ItemCategorySegment2 = 'ECONOMIC DEVELOPMENT'
SET @IsActive = 1
SET @IsRevenueDesignation = 1
SET @Goal = 2286116
SET @StartDate = '2012-01-01'
SET @EndDate = '2013-01-01'
--SET @ReceivedAmount = 522985
*/




/* DESIGNATIONLEVELTYPEID */
DECLARE @DesignationLevelTypeId uniqueidentifier
SET @DesignationLevelTypeId = (SELECT ID FROM DESIGNATIONLEVELTYPE WHERE DESCRIPTION = 'Fund')

/* DESIGNATIONLEVELID1 */
DECLARE @DesignationLevelId1 uniqueidentifier
INSERT INTO DESIGNATIONLEVEL(NAME, DESCRIPTION, DESIGNATIONLEVELTYPEID, USERID, VANITYNAME, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
VALUES (@LookupId, @LookupId, @DesignationLevelTypeId, @LookupId, @LookupId, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
--VALUES ('D50194029', 'D50194029', 'D3F177E3-634D-44E4-AA6F-C11A522CF592', 'D50194029', 'D50194029', @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
SET @DesignationLevelId1 = (SELECT ID FROM DESIGNATIONLEVEL WHERE DATEADDED = @CURRENTDATE)
--SET @DesignationLevelId1 = (SELECT ID FROM DESIGNATIONLEVEL WHERE NAME = @LookupId)

/* DESIGNATIONREPORTCODE2TYPEID */
DECLARE @DesignationReportCode2TypeId uniqueidentifier
IF (SELECT COUNT(*) FROM DESIGNATIONREPORT2CODE WHERE DESCRIPTION = @SubReg1) = 0 BEGIN
	INSERT INTO DESIGNATIONREPORT2CODE(DESCRIPTION, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
	VALUES (@SubReg1, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
	SET @DesignationReportCode2TypeId = (SELECT ID FROM DESIGNATIONREPORT2CODE WHERE DATEADDED = @CURRENTDATE)
END ELSE BEGIN
	SET @DesignationReportCode2TypeId = (SELECT TOP 1 ID FROM DESIGNATIONREPORT2CODE WHERE DESCRIPTION = @SubReg1)
END

/* VANITYNAME */
DECLARE @VanityName varchar(512)
SET @VanityName = @LookupId + '_' + @Description

/* DESIGNATIONUSECODEID*/
DECLARE @DesignationUseCodeId uniqueidentifier
IF (SELECT COUNT(*) FROM DESIGNATIONUSECODE WHERE DESCRIPTION = @Priority) = 0 BEGIN
	INSERT INTO DESIGNATIONUSECODE(DESCRIPTION, ADDEDBYID,      CHANGEDBYID,    DATEADDED,    DATECHANGED)
	VALUES                        (@Priority,	@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
	SET @DesignationUseCodeId = (SELECT ID FROM DESIGNATIONUSECODE WHERE DATEADDED = @CURRENTDATE)
END ELSE BEGIN
	SET @DesignationUseCodeId = (SELECT ID FROM DESIGNATIONUSECODE WHERE DESCRIPTION = @Priority)
END

/* DESIGNATION */
DECLARE @DesignationId uniqueidentifier
INSERT INTO DESIGNATION (DESIGNATIONLEVEL1ID, DESIGNATIONREPORT2CODEID, ISACTIVE, USERID, VANITYNAME, DESIGNATIONUSECODEID, ISREVENUEDESIGNATION, STARTDATE, ENDDATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
VALUES(@DesignationLevelId1, @DesignationReportCode2TypeId, @IsActive, @LookupId, @VanityName, @DesignationUseCodeId, @IsRevenueDesignation, @StartDate, @EndDate, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE) 
SET @DesignationId = (SELECT ID FROM DESIGNATION WHERE DATEADDED = @CURRENTDATE)


DECLARE @CampaignId uniqueidentifier
IF @LookupId LIKE 'D3%' OR (@LookupId LIKE 'D5%' AND @FundraisingTeam = 'Major Donor Ministries' AND @Priority = 'High') BEGIN
	
	IF @LookupId LIKE 'D3%' BEGIN
		SET @CampaignId = (SELECT ID FROM CAMPAIGN WHERE NAME = 'Rebuilding Hope at Home')		
	END
	
	IF @LookupId LIKE 'D5%' BEGIN
		SET @CampaignId = (SELECT ID FROM CAMPAIGN WHERE NAME = 'World Vision For Every Child')		
	END

	DECLARE @CampaignSubPriorityId uniqueidentifier
	IF (SELECT COUNT(*) FROM CAMPAIGNSUBPRIORITY WHERE NAME = @SubReg1) = 0 BEGIN
		
		DECLARE @CampaignSubPriorityNameCodeId uniqueidentifier
		IF (SELECT COUNT(*) FROM CAMPAIGNSUBPRIORITYNAMECODE WHERE DESCRIPTION = @SubReg1) = 0 BEGIN
			INSERT INTO CAMPAIGNSUBPRIORITYNAMECODE (DESCRIPTION, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
			VALUES (@SubReg1, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
			SET @CampaignSubPriorityNameCodeId = (SELECT ID FROM CAMPAIGNSUBPRIORITYNAMECODE WHERE DATEADDED = @CURRENTDATE)
		END ELSE BEGIN
			SET @CampaignSubPriorityNameCodeId = (SELECT ID FROM CAMPAIGNSUBPRIORITYNAMECODE WHERE DESCRIPTION = @SubReg1)
		END


		/* CAMPAIGNPRIORITYTYPECODEID */
		DECLARE @CampaignPriorityTypeCodeId uniqueidentifier
		IF (SELECT COUNT(*) FROM CAMPAIGNPRIORITYTYPECODE WHERE DESCRIPTION = @ItemCategorySegment2) = 0 BEGIN
			INSERT INTO CAMPAIGNPRIORITYTYPECODE (DESCRIPTION, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
			VALUES (@ItemCategorySegment2, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
			SET @CampaignPriorityTypeCodeId = (SELECT ID FROM CAMPAIGNPRIORITYTYPECODE WHERE DATEADDED = @CURRENTDATE)
		END ELSE BEGIN
			SET @CampaignPriorityTypeCodeId = (SELECT ID FROM CAMPAIGNPRIORITYTYPECODE WHERE DESCRIPTION = @ItemCategorySegment2)
		END
		
		
		/* CAMPAIGNPRIORITY */
		DECLARE @CampaignPriorityId uniqueidentifier
		IF (SELECT COUNT(*) FROM CAMPAIGNPRIORITY WHERE CAMPAIGNPRIORITYTYPECODEID = @CampaignPriorityTypeCodeId) = 0 BEGIN
			INSERT INTO CAMPAIGNPRIORITY (CAMPAIGNID,  CAMPAIGNPRIORITYTYPECODEID,  GOAL,  ADDEDBYID,      CHANGEDBYID,    DATEADDED,    DATECHANGED)
			VALUES                       (@CampaignId, @CampaignPriorityTypeCodeId, @Goal, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
			SET @CampaignPriorityId = (SELECT ID FROM CAMPAIGNPRIORITY WHERE DATEADDED = @CURRENTDATE)
		END ELSE BEGIN
			SET @CampaignPriorityId = (SELECT TOP 1 ID FROM CAMPAIGNPRIORITY WHERE CAMPAIGNPRIORITYTYPECODEID = @CampaignPriorityTypeCodeId)
		END

		INSERT INTO CAMPAIGNSUBPRIORITY(CAMPAIGNPRIORITYID, GOAL, CAMPAIGNSUBPRIORITYNAMECODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
		VALUES (@CampaignPriorityId, @Goal, @CampaignSubPriorityNameCodeId, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
		SET @CampaignSubPriorityId = (SELECT ID FROM CAMPAIGNSUBPRIORITY WHERE DATEADDED = @CURRENTDATE)
		
	END ELSE BEGIN
		SET @CampaignSubPriorityId = (SELECT ID FROM CAMPAIGNSUBPRIORITY WHERE NAME = @SubReg1)
	END

	INSERT INTO DESIGNATIONCAMPAIGN (DESIGNATIONID, CAMPAIGNID, CAMPAIGNSUBPRIORITYID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
	VALUES (@DesignationId, @CampaignId, @CampaignSubPriorityId, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)

END









end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0				
				]]>
			</common:CreateProcedureSQL>
		</SaveImplementation>
	</SPDataForm>

	<!-- describe fields on the form, which correspond to parameters on the SP.  Note that system parameters 
	like the context @ID, @CONTEXTID, and @CURRENTAPPUSERID need not be listed. -->
	<common:FormMetaData FixedDialog="true">
		<common:FormFields>

<!--      <common:FormField FieldID="ID uniqueidentifier = null output,
            <common:FormField FieldID="CONTEXTID uniqueidentifier,
            <common:FormField FieldID="CHANGEAGENTID uniqueidentifier = null,-->
            <common:FormField FieldID="Description" Caption="Description" DataType="String" MaxLength="255" />
            <common:FormField FieldID="SubReg1" Caption="SubReg1" DataType="String" MaxLength="255" />
            <common:FormField FieldID="LookupID" Caption="LookupID" DataType="String" MaxLength="255" />
            <common:FormField FieldID="Priority" Caption="Priority" DataType="String" MaxLength="100" />
            <common:FormField FieldID="ItemCategorySegment2" Caption="ItemCategorySegment2" DataType="String" MaxLength="255" />
            <common:FormField FieldID="FundraisingTeam" Caption="FundraisingTeam" DataType="String" MaxLength="255" />
            <common:FormField FieldID="IsActive" Caption="IsActive" DataType="Boolean" />
            <common:FormField FieldID="IsRevenueDesignation" Caption="IsRevenueDesignation" DataType="Boolean" />
            <common:FormField FieldID="Goal" Caption="Goal" DataType="Money" />
            <common:FormField FieldID="StartDate" Caption="StartDate" DataType="Date" />
            <common:FormField FieldID="EndDate" Caption="EndDate" DataType="Date" />
            <common:FormField FieldID="ReceivedAmount" Caption="ReceivedAmount" DataType="Money" />
		</common:FormFields>

	</common:FormMetaData>

</AddDataFormTemplateSpec>